Background:
- I'm running MSDE 2000 (not client tools, stored procedure capability,
etc). This may change, but not in the first part of development.
- My Access file is an Access 2002 project (.adp project client
connecting directly to MSDE SQL database - no .mdb involved or local
file tables beyond the .dbfs to be imported).
- Using ODBC to connect to .dbf data sources without a problem (ODBC is
working).
- Using ODBC to connect to MSDE SQL remote server without a problem.
Connections test fine, manual import from within Access works perfectly
(ODBC is working here as well).
Goal:
- To be able to import .dbf files programmatically into MSDE SQL tables
with Access vba. I stress programatically, as the updates will take
place nightly on a scheduled basis (as they currently do in a current
non-sql database system).
Problem:
Whenever I try to use the DoCmd.TransferDatabase function within Access
2002, I receive:
"Run-time error '3011':
The Microsoft Jet database engine could not find the object
'tblMySQLtableName'. Make sure the object exists and that you spell
its name and the path name correctly."
The code that I'm using is as follows:
DoCmd.TransferDatabase acImport, "ODBC", _
"ODBC;DSN=myDSN;SourceD*B=h:\myPath\;SourceType=DB F", _
acTable, "sourcetable.dbf", "tblMySQLtableName"
Linking to external tables isn't allowed by Access as the database is
currently set up.
Additionally, if I have the table, tblMySQLtableName, already created
within the SQL database, the error message indicates that the
tblMySQLtableName1 object could not be found. To me, this indicates
that the source and destination are both recognized and the
TransferDatabase function is attempting (properly so) to create a new
table rather than overwriting or appending to an existing table, but it
stalls on the creation of the new table.
Question:
Does anyone have suggestions (read: example code) of what might be a
good way to either overcome the above problem or perhaps a better way
of accomplishing the same goal, keeping in mind that stored procedures
and most of the DTS functionality isn't available in MSDE?
Thanks in advance,
JBH